-- @owner: wang-tianjing1
-- @date: 2024/8/19
-- @testpoint:show grants--访问数据库、函数、过程语言的权限

@conn PrimaryDbBmode;
--step1:创建用户,expect:成功
set b_compatibility_user_host_auth = on;
set dolphin.b_compatibility_mode=off;
set current_schema to public;
show current_schema;
drop user if exists u_sg_tester04 cascade;
create user u_sg_tester04 identified by 'password@123';
show grants for 'u_sg_tester04';

--step2:创建数据库,expect:成功
drop database if exists d_database_0004;
create database d_database_0004;
show grants for 'u_sg_tester04';
select datacl from pg_database where datname = 'd_database_0004';
show current_schema;

--step3:授予权限,expect:成功
grant create,connect,temp,temporary,alter,drop,comment on database d_database_0004 to u_sg_tester04;
show grants for 'u_sg_tester04';
grant create,connect,temp,temporary,alter,drop,comment on database d_database_0004 to u_sg_tester04 with grant option;
show grants for 'u_sg_tester04';
select datacl from pg_database where datname = 'd_database_0004';
revoke create,connect,temp,temporary,alter,drop,comment on database d_database_0004 from u_sg_tester04;
show grants for 'u_sg_tester04';
select datacl from pg_database where datname = 'd_database_0004';
grant all privileges on database d_database_0004 to u_sg_tester04;
show grants for 'u_sg_tester04';
grant all privileges on database d_database_0004 to u_sg_tester04 with grant option;
show grants for 'u_sg_tester04';
select datacl from pg_database where datname = 'd_database_0004';
revoke all privileges on database d_database_0004 from u_sg_tester04;
show grants for 'u_sg_tester04';
select datacl from pg_database where datname = 'd_database_0004';

--step4:授权创建函数,expect:合理报错
grant create routine to u_sg_tester04;
show grants for 'u_sg_tester04';
set session authorization u_sg_tester04 password 'password@123';
set current_schema to u_sg_tester04;
show current_schema;
drop function if exists func_function_0004();
create function func_function_0004()returns date as $$begin return now();
end;
$$ language plpgsql;
/

--step5:授予函数权限,expect:成功
reset session authorization;
show current_schema;
grant all on function func_function_0004() to u_sg_tester04;
show grants for 'u_sg_tester04';
select prosrc from pg_proc where proname = 'func_function_0004';
revoke all on function func_function_0004() from u_sg_tester04;
revoke create any function from u_sg_tester04;
show grants for 'u_sg_tester04';
select prosrc from pg_proc where proname = 'func_function_0004';
grant all privileges on function func_function_0004() to u_sg_tester04;
show grants for 'u_sg_tester04';
grant all privileges on function func_function_0004() to u_sg_tester04
with grant option;
show grants for 'u_sg_tester04';
select prosrc from pg_proc where proname = 'func_function_0004';
revoke all privileges on function func_function_0004() from u_sg_tester04;
show grants for 'u_sg_tester04';
select prosrc from pg_proc where proname = 'func_function_0004';
grant alter routine,alter,drop,comment on function func_function_0004()
to u_sg_tester04;
show grants for 'u_sg_tester04';
grant alter routine,alter,drop,comment on function func_function_0004()
to u_sg_tester04 with grant option;
show grants for 'u_sg_tester04';
select prosrc from pg_proc where proname = 'func_function_0004';
revoke alter,drop,comment on function func_function_0004() from u_sg_tester04;
show grants for 'u_sg_tester04';
select prosrc from pg_proc where proname = 'func_function_0004';
--step6:授予过程语言权限,expect:成功
grant usage on language plpgsql to u_sg_tester04;
show grants for 'u_sg_tester04';
grant usage on language plpgsql to u_sg_tester04 with grant option;
show grants for 'u_sg_tester04';
select lanacl from pg_language where lanname = 'plpgsql';
revoke usage on language plpgsql from u_sg_tester04;show grants for 'u_sg_tester04';
select lanacl from pg_language where lanname = 'plpgsql';
grant all on language plpgsql to u_sg_tester04;
show grants for 'u_sg_tester04';
grant all privileges on language plpgsql to u_sg_tester04 with grant option;
show grants for 'u_sg_tester04';
select lanacl from pg_language where lanname = 'plpgsql';
revoke all on language plpgsql from u_sg_tester04;
show grants for 'u_sg_tester04';
select lanacl from pg_language where lanname = 'plpgsql';

--step7:清理环境,expect:成功
drop database if exists d_database_0004;
drop function if exists func_function_0004();
set current_schema to public;
drop owned by u_sg_tester04 cascade;
drop user u_sg_tester04;





























